﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using QLHD.Entity.Manipulation;

namespace QLHD.DAL.Manipulation
{
    public class HopDong_DAL
    {
        const string spHopDong_CountHDByDanhMucHopDong = "sp_HopDong_CountHDByDanhMucHopDong";
        const string sp_HopDong_List = "sp_HopDong_List";
        const string sp_HopDong_GetByID = "sp_HopDong_GetByID";
        const string sp_HopDong_Insert = "sp_HopDong_Insert";
        const string sp_HopDong_Update = "sp_HopDong_Update";
        const string sp_HopDong_UpdateSale = "sp_HopDong_UpdateSale";
        const string sp_HopDong_UpdateKhachHang = "sp_HopDong_UpdateKhachHang";
        const string sp_HopDong_Delete = "sp_HopDong_Delete";

        const string sp_HopDongChiTiet_GetByHopDongID = "sp_HopDongChiTiet_GetByHopDongID";
        const string sp_HopDongChiTiet_Save = "sp_HopDongChiTiet_Save";
        const string sp_HopDongChiTiet_Delete = "sp_HopDongChiTiet_Delete";


        const string sp_ThongTinThanhToan_GetByHopDongID = "sp_ThongTinThanhToan_GetByHopDongID";
        const string sp_ThongTinThanhToan_Insert = "sp_ThongTinThanhToan_Insert";
        const string sp_ThongTinThanhToan_Update = "sp_ThongTinThanhToan_Update";
        const string sp_ThongTinThanhToan_Delete = "sp_ThongTinThanhToan_Delete";


        public static string HopDong_CountHDByDanhMucHopDong(long DanhMucHopDongID)
        {
            DataTable dt = new DataTable();
            using (MainDB db = new MainDB())
            {
                dt = db.StoredProcedures.HopDong_GetTotal(DanhMucHopDongID);
            }
            if (dt != null && dt.Rows.Count > 0)
            {
                if (dt.Rows[0][0].ToString() == "0") return "01";
                return dt.Rows[0][0].ToString().Length == 1 ? "0" + (dt.Rows[0][0].DChangeType<int>() + 1).ToString() : (dt.Rows[0][0].DChangeType<int>() + 1).ToString();
            }
            else
                return "01";

        }

        public static long LuuHopdong(QLHD.Entity.Manipulation.HopDong.objHD objHD, int status)
        {
            long HopDongID = objHD.HopDongID > 0 ? objHD.HopDongID : 0, ChitietHdID = 0, ThongTinThanhToanID = 0;
            if (HopDongID > 0)
                Update_Hopdong(objHD, status);
            else
                HopDongID = Insert_Hopdong(objHD, status);

            foreach (HopDong.NoiDungHD noidungHD in objHD.NoiDungHD)
            {
                ChitietHdID = noidungHD.HopDongChiTietID > 0 ? noidungHD.HopDongChiTietID : 0;

                if (ChitietHdID > 0 && noidungHD.IsDelete == 1)
                    Delete_HopdongChiTiet(HopDongID, noidungHD);
                else if (ChitietHdID > 0)
                    Update_HopdongChiTiet(HopDongID, noidungHD);
                else
                    ChitietHdID = Insert_HopdongChiTiet(HopDongID, noidungHD);
            }

            foreach (HopDong.SoLanTT solanTT in objHD.SoLanTT)
            {
                ThongTinThanhToanID = solanTT.ThongTinThanhToanID > 0 ? solanTT.ThongTinThanhToanID : 0;

                if (ThongTinThanhToanID > 0 && solanTT.IsDelete == 1)
                    Delete_ThongTinThanhToan(HopDongID, solanTT);
                else if (ThongTinThanhToanID > 0)
                    Update_ThongTinThanhToan(HopDongID, solanTT);
                else
                    ThongTinThanhToanID = Insert_ThongTinThanhToan(HopDongID, solanTT);
            }

            return HopDongID;
        }

        public static HopDong.objHD EditHopdong(long HopDongID)
        {
            HopDong.objHD objHD = GetHopDongByHopDongID(HopDongID);
            objHD.NoiDungHD = GetNoiDungHopDongByHopDongID(HopDongID);
            objHD.SoLanTT = GetSoLanTTByHopDongID(HopDongID);
            if (objHD.NoiDungHD.Count > 0)
            {
                var countNoiDungHD = (from p in objHD.NoiDungHD
                                      orderby p.HopDongChiTietID descending
                                      select p).First();
                objHD.countNoiDungHD = countNoiDungHD.id;
            }

            if (objHD.SoLanTT.Count > 0)
            {
                var countSoLanTT = (from p in objHD.SoLanTT
                                    orderby p.ThongTinThanhToanID descending
                                    select p).First();
                objHD.countSoLanTT = countSoLanTT.id;
            }
            else
                objHD.countSoLanTT = 1;
            return objHD;
        }

        public static HopDong.objHD GetHopDongByHopDongID(long HopDongID)
        {
            try
            {
                var parameters = new[] { new SqlParameter("@HopDongID", HopDongID) };
                return DbCommon.ExecuteSingle<HopDong.objHD>(DbCommon.GetConnection(), sp_HopDong_GetByID, parameters);
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format("{0}:: {1}", sp_HopDong_GetByID, ex.Message));
            }
        }

        public static List<HopDong.NoiDungHD> GetNoiDungHopDongByHopDongID(long HopDongID)
        {
            try
            {
                var parameters = new[] { new SqlParameter("@HopDongID", HopDongID) };
                return DbCommon.ExecuteList<HopDong.NoiDungHD>(DbCommon.GetConnection(), sp_HopDongChiTiet_GetByHopDongID, parameters);
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format("{0}:: {1}", sp_HopDongChiTiet_GetByHopDongID, ex.Message));
            }
        }

        public static List<HopDong.SoLanTT> GetSoLanTTByHopDongID(long HopDongID)
        {
            try
            {
                var parameters = new[] { new SqlParameter("@HopDongID", HopDongID) };
                return DbCommon.ExecuteList<HopDong.SoLanTT>(DbCommon.GetConnection(), sp_ThongTinThanhToan_GetByHopDongID, parameters);
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format("{0}:: {1}", sp_ThongTinThanhToan_GetByHopDongID, ex.Message));
            }
        }



        public static long Insert_Hopdong(QLHD.Entity.Manipulation.HopDong.objHD objHD, int status)
        {
            DataTable dt = new DataTable();
            using (MainDB db = new MainDB())
            {
                dt = db.StoredProcedures.Insert_Hopdong(objHD, status);
            }
            if (dt != null && dt.Rows.Count > 0)
            {
                return Convert.ToInt64(dt.Rows[0][0].ToString());
            }
            else
                return -1;
        }

        public static void Update_Hopdong(QLHD.Entity.Manipulation.HopDong.objHD objHD, int status)
        {
            DataTable dt = new DataTable();
            using (MainDB db = new MainDB())
            {
                dt = db.StoredProcedures.Update_Hopdong(objHD, status);
            }
        }

        public static bool Delete_Hopdong(long hopdongID)
        {
            try
            {
                var parameters = new[] { new SqlParameter("@HopDongID", hopdongID) };
                var numberRows = SqlHelper.ExecuteNonQuery(DbCommon.GetConnection(), CommandType.StoredProcedure, sp_HopDong_Delete, parameters);
                return numberRows > 0;
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format("{0}:: {1}", sp_HopDong_Delete, ex.Message));
            }
        }

        public static bool UpdateSale(long hopdongID, long saleID)
        {
            try
            {
                var parameters = new[] { new SqlParameter("@HopDongID", hopdongID), new SqlParameter("@NhanSuREF", saleID) };

                var numberRows = SqlHelper.ExecuteNonQuery(DbCommon.GetConnection(), CommandType.StoredProcedure, sp_HopDong_UpdateSale, parameters);
                return numberRows > 0;
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format("{0}:: {1}", sp_HopDong_UpdateSale, ex.Message));
            }
        }

        public static bool UpdateKhachHang(long hopdongID, long khachhangID)
        {
            try
            {
                var parameters = new[] { new SqlParameter("@HopDongID", hopdongID), new SqlParameter("@KhachHangREF", khachhangID) };
                var numberRows = SqlHelper.ExecuteNonQuery(DbCommon.GetConnection(), CommandType.StoredProcedure, sp_HopDong_UpdateKhachHang, parameters);
                return numberRows > 0;
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format("{0}:: {1}", sp_HopDong_UpdateKhachHang, ex.Message));
            }
        }


        public static long Insert_HopdongChiTiet(long HopDongID, HopDong.NoiDungHD noidungHD)
        {
            DataTable dt = new DataTable();
            using (MainDB db = new MainDB())
            {
                dt = db.StoredProcedures.Insert_NoidungHopdong(HopDongID, noidungHD);
            }
            if (dt != null && dt.Rows.Count > 0)
            {
                return Convert.ToInt64(dt.Rows[0][0].ToString());
            }
            else
                return -1;
        }

        public static void Update_HopdongChiTiet(long HopDongID, HopDong.NoiDungHD noidungHD)
        {
            DataTable dt = new DataTable();
            using (MainDB db = new MainDB())
            {
                dt = db.StoredProcedures.Update_NoidungHopdong(HopDongID, noidungHD);
            }
        }

        public static void Delete_HopdongChiTiet(long HopDongID, HopDong.NoiDungHD noidungHD)
        {
            DataTable dt = new DataTable();
            using (MainDB db = new MainDB())
            {
                dt = db.StoredProcedures.Delete_NoidungHopdong(HopDongID, noidungHD);
            }
        }

        public static long Insert_ThongTinThanhToan(long HopDongID, HopDong.SoLanTT solanTT)
        {
            DataTable dt = new DataTable();
            using (MainDB db = new MainDB())
            {
                dt = db.StoredProcedures.Insert_ThongTinThanhToan(HopDongID, solanTT);
            }
            if (dt != null && dt.Rows.Count > 0)
            {
                return Convert.ToInt64(dt.Rows[0][0].ToString());
            }
            else
                return -1;
        }

        public static void Update_ThongTinThanhToan(long HopDongID, HopDong.SoLanTT solanTT)
        {
            DataTable dt = new DataTable();
            using (MainDB db = new MainDB())
            {
                dt = db.StoredProcedures.Update_ThongTinThanhToan(HopDongID, solanTT);
            }
        }

        public static void Delete_ThongTinThanhToan(long HopDongID, HopDong.SoLanTT solanTT)
        {
            DataTable dt = new DataTable();
            using (MainDB db = new MainDB())
            {
                dt = db.StoredProcedures.Delete_ThongTinThanhToan(HopDongID, solanTT);
            }
        }

        private const string Table = "HopDong INNER JOIN  NhanSu NS ON HopDong.NhanSuREF = NS.NhanSuID  INNER JOIN  KhachHang KH ON HopDong.KhachHangREF = KH.KhachHangID";
        private const string TableOrder = "HopDong.HopDongID DESC";
        private const string FieldList = "HopDongID,SoHopDong as 'SoHD',NS.TenNhanSu,NS.TenBoPhan,KH.TenKhachHang,(SELECT TOP 1 nh.TenNhanHang FROM HopDongChiTiet hdct INNER JOIN NhanHang nh ON hdct.NhanHangREF = nh.NhanHangID WHERE hdct.HopDongREF = HopDongID) AS 'NhanHang',GiaTriHopDong as 'GiaTriHD'";

        public static List<HopDong.ListHD> ListHopdong(string keyword , int pageIndex , int pageSize,out int totalRecords)
        {
            try
            {
                var filter = "1=1";
                if (!string.IsNullOrEmpty(keyword))
                    filter += string.Format(" AND ((SoHopDong LIKE N'%{0}%') OR (NS.TenNhanSu LIKE N'%{0}%') OR (NS.TenBoPhan LIKE N'%{0}%')  OR (KH.TenKhachHang LIKE N'%{0}%'))", keyword);

                var lstArticle = DbCommon.ExecutePagination<HopDong.ListHD>(DbCommon.GetConnection(), Table, filter,
                                                                        FieldList, TableOrder, pageIndex, pageSize, out totalRecords);

                return lstArticle;    
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format("{0}:: {1}", sp_HopDong_List, ex.Message));
            }
        }
    }
}
